﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Backend.DTO;
using System.Data;
using System.Data.SqlClient;

namespace Backend.DAL
{
    class ListDAL
    {
        private SqlConnection sqlConnection;

        public ListDAL()
        {
            DbConn aConn = new DbConn();
            sqlConnection = aConn.GetUssdSqlConnection;
        }

        #region GenericLists

        public GenericListDTO GetGenericList(int id)
        {
            GenericListDTO aGenericListDTO = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM GenericLists WHERE Id = @Id", sqlConnection);
                command.Parameters.AddWithValue("Id", id);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        aGenericListDTO = new GenericListDTO();
                        aGenericListDTO.Id = Convert.ToInt32(reader["Id"]);
                        aGenericListDTO.Name = reader["Name"].ToString();
                        aGenericListDTO.Description = reader["Description"].ToString();
                        aGenericListDTO.Type = Convert.ToInt32(reader["Type"]);

                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return aGenericListDTO;
        }

        public GenericListDTO GetGenericList(int id, int listType)
        {
            GenericListDTO aGenericListDTO = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM GenericLists WHERE Id = @Id AND Type = @Type", sqlConnection);
                command.Parameters.AddWithValue("Id", id);
                command.Parameters.AddWithValue("Type", listType);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        aGenericListDTO = new GenericListDTO();
                        aGenericListDTO.Id = Convert.ToInt32(reader["Id"]);
                        aGenericListDTO.Name = reader["Name"].ToString();
                        aGenericListDTO.Description = reader["Description"].ToString();
                        aGenericListDTO.Type = Convert.ToInt32(reader["Type"]);

                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return aGenericListDTO;
        }

        public List<GenericListDTO> GetGenericLists(int listType)
        {
            List<GenericListDTO> listOfGenericLists = new List<GenericListDTO>();

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM GenericLists WHERE Type = @ListType", sqlConnection);
                command.Parameters.AddWithValue("ListType", listType);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        GenericListDTO aGenericListDTO = new GenericListDTO();
                        aGenericListDTO.Id = Convert.ToInt32(reader["Id"]);
                        aGenericListDTO.Name = reader["Name"].ToString();
                        aGenericListDTO.Description = reader["Description"].ToString();
                        aGenericListDTO.Type = Convert.ToInt32(reader["Type"]);

                        listOfGenericLists.Add(aGenericListDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfGenericLists;
        }

        public List<GenericListDTO> GetGenericLists(int listType, string keyword, int maximumResult)
        {
            List<GenericListDTO> listOfGenericLists = new List<GenericListDTO>();

            keyword = "%" + keyword + "%";

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT TOP(@NumberOfresult) * FROM GenericLists WHERE Type = @ListType AND (Name LIKE @Keyword OR Description LIKE @Keyword)", sqlConnection);
                command.Parameters.AddWithValue("NumberOfresult", maximumResult);
                command.Parameters.AddWithValue("ListType", listType);
                command.Parameters.AddWithValue("Keyword", keyword);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        GenericListDTO aGenericListDTO = new GenericListDTO();
                        aGenericListDTO.Id = Convert.ToInt32(reader["Id"]);
                        aGenericListDTO.Name = reader["Name"].ToString();
                        aGenericListDTO.Description = reader["Description"].ToString();
                        aGenericListDTO.Type = Convert.ToInt32(reader["Type"]);

                        listOfGenericLists.Add(aGenericListDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfGenericLists;
        }

        #endregion


        #region Pharmaceuticals Lists

        public PharmaceuticalsListsDTO GetPharmaceuticalsListsById(int id)
        {
            PharmaceuticalsListsDTO aPharmaceuticalsListsDTO = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM PharmaceuticalsLists WHERE Id = @Id", sqlConnection);
                command.Parameters.AddWithValue("Id", id);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        aPharmaceuticalsListsDTO = new PharmaceuticalsListsDTO();
                        aPharmaceuticalsListsDTO.Id = Convert.ToInt32(reader["Id"]);
                        aPharmaceuticalsListsDTO.Name = reader["Name"].ToString();
                        aPharmaceuticalsListsDTO.Substance = reader["Substance"].ToString();
                        aPharmaceuticalsListsDTO.AtnName = reader["AtnName"].ToString();
                        aPharmaceuticalsListsDTO.Indication = reader["Indication"].ToString();
                        aPharmaceuticalsListsDTO.Atc = reader["Atc"].ToString();

                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return aPharmaceuticalsListsDTO;
        }

        public List<PharmaceuticalsListsDTO> GetPharmaceuticalLists()
        {
            List<PharmaceuticalsListsDTO> listOfPharmaceuticalsLists = new List<PharmaceuticalsListsDTO>();

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM PharmaceuticalsLists", sqlConnection);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        PharmaceuticalsListsDTO aPharmaceuticalsListDTO = new PharmaceuticalsListsDTO();
                        aPharmaceuticalsListDTO.Id = Convert.ToInt32(reader["Id"]);
                        aPharmaceuticalsListDTO.Name = reader["Name"].ToString();
                        aPharmaceuticalsListDTO.Substance = reader["Substance"].ToString();
                        aPharmaceuticalsListDTO.AtnName = reader["AtnName"].ToString();
                        aPharmaceuticalsListDTO.Indication = reader["Indication"].ToString();
                        aPharmaceuticalsListDTO.Atc = reader["Atc"].ToString();

                        listOfPharmaceuticalsLists.Add(aPharmaceuticalsListDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfPharmaceuticalsLists;
        }

        public List<PharmaceuticalsListsDTO> GetPharmaceuticalLists(string keyword, int maximumResult)
        {
            List<PharmaceuticalsListsDTO> listOfPharmaceuticalsLists = new List<PharmaceuticalsListsDTO>();

            keyword = "%" + keyword + "%";

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT TOP(@NumberOfresult) * FROM PharmaceuticalsLists WHERE Name LIKE @Keyword", sqlConnection);
                command.Parameters.AddWithValue("NumberOfresult", maximumResult);
                command.Parameters.AddWithValue("Keyword", keyword);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        PharmaceuticalsListsDTO aPharmaceuticalsListsDTO = new PharmaceuticalsListsDTO();
                        aPharmaceuticalsListsDTO.Id = Convert.ToInt32(reader["Id"]);
                        aPharmaceuticalsListsDTO.Name = reader["Name"].ToString();
                        aPharmaceuticalsListsDTO.Substance = reader["Substance"].ToString();
                        aPharmaceuticalsListsDTO.AtnName = reader["AtnName"].ToString();
                        aPharmaceuticalsListsDTO.Indication = reader["Indication"].ToString();
                        aPharmaceuticalsListsDTO.Atc = reader["Atc"].ToString();

                        listOfPharmaceuticalsLists.Add(aPharmaceuticalsListsDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfPharmaceuticalsLists;
        }

        #endregion
    }
}
